﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_AllotServiceCount]
	(
		@CompanyId int,
		@DepId int,
		@StateId int,
		@TypeId int,
		@WayId int,
		@ProcessId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10)
	)
AS
begin
	/*Departments*/
--	declare @indextable table(id int identity(1,1),nid int)
--	;WITH ChildDepartment(ID,DName,PId) AS
--	(
--		SELECT ID,DName,ParentID FROM department WHERE id=@DepId
--	    
--		UNION ALL
--		SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
--		INNER JOIN department ON d.ID = department.ParentID
--	)
--	
--	insert into @indextable(nid) select Id from ChildDepartment;
	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId

	Select Count(Id) From [Order]
	Where (@CompanyId=0 Or CompanyId=@CompanyId) and (@DepId=0 Or DepId in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
		and (@StateId=0 Or StateId=@StateId) and (@TypeId=0 Or TypeId=@TypeId) and (@WayId=0 Or WayId=@WayId) and (@ProcessId=0 Or ProcessId=@ProcessId)
		and (Convert(nchar(10),TradeDate,120) between @StartDate and @EndDate)
		and (@Stext='' Or CusName like '%'+@Stext+'%' Or Salesman like '%'+@Stext+'%')
end
	RETURN
